import pandas as pd

import DataUtil
from UtilTool import DataDIC


class DataAnalysis:
    def __init__(self):
        self.dataUtil = DataUtil.DataUtil()

    # 查询每个省份用户数量
    def query_province_user_count(self):
        data = self.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv')
        data.columns = ['ip', 'province', 'city', 'access', 'mobile', 'browser', 'gender', 'age', 'method', 'url',
                        'code']
        province_count = data.groupby('province').size()
        province_count = province_count.reset_index(name='count')
        # 根据DataDIC文件，将对应名字映射
        province_count['province'] = province_count['province'].map(DataDIC.province_dic)
        # print(province_count)
        self.dataUtil.save_data_to_csv(province_count, '../data/province_user_count.csv')
        self.dataUtil.save_data_to_db(province_count, 'province_user_count')

    # 查询每个省份的用户数量，并按照性别进行细分
    def query_province_user_count_by_gender(self):
        data = self.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv')
        data.columns = ['ip', 'province', 'city', 'access', 'mobile', 'browser', 'gender', 'age', 'method', 'url',
                        'code']
        province_count = data.groupby(['province', 'gender']).size().reset_index(name='count')
        self.dataUtil.save_data_to_csv(province_count, '../data/province_user_count_by_gender.csv')
        self.dataUtil.save_data_to_db(province_count, 'province_user_count_by_gender')

    # 查询男女上网比例
    def query_gender_ratio(self):
        data = self.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv')
        data.columns = ['ip', 'province', 'city', 'access', 'mobile', 'browser', 'gender', 'age', 'method', 'url',
                        'code']
        gender_ratio = data.groupby('gender').size().reset_index(name='count')
        print(gender_ratio)
        gender_ratio['ratio'] = gender_ratio['count'] / gender_ratio['count'].sum()
        self.dataUtil.save_data_to_csv(gender_ratio, '../data/gender_ratio.csv')
        self.dataUtil.save_data_to_db(gender_ratio, 'gender_ratio')

    # 查询最受欢迎的手机，并进行排名
    def query_most_popular_mobile(self):
        data = self.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv')
        data.columns = ['ip', 'province', 'city', 'access', 'mobile', 'browser', 'gender', 'age', 'method', 'url',
                        'code']
        mobile_count = data.groupby('mobile').size().reset_index(name='count')
        mobile_count = mobile_count.sort_values(by='count', ascending=False)
        mobile_count['mobile'] = mobile_count['mobile'].map(DataDIC.mobile_dic)
        self.dataUtil.save_data_to_csv(mobile_count, '../data/most_popular_mobile.csv')
        mobile_count = mobile_count.head(10)
        self.dataUtil.save_data_to_db(mobile_count, 'most_popular_mobile')

    # 查询最受欢迎的浏览器，并进行排名
    def query_most_popular_browser(self):
        data = self.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv')
        data.columns = ['ip', 'province', 'city', 'access', 'mobile', 'browser', 'gender', 'age', 'method', 'url',
                        'code']
        browser_count = data.groupby('browser').size().reset_index(name='count')
        browser_count = browser_count.sort_values(by='count', ascending=False)
        browser_count['browser'] = browser_count['browser'].map(DataDIC.browser_dic)
        self.dataUtil.save_data_to_csv(browser_count, '../data/most_popular_browser.csv')
        browser_count = browser_count.head(10)
        self.dataUtil.save_data_to_db(browser_count, 'most_popular_browser')

    # 统计出男生(女生),最受欢迎的手机
    def query_most_popular_mobile_by_gender(self):
        data = self.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv')
        data.columns = ['ip', 'province', 'city', 'access', 'mobile', 'browser', 'gender', 'age', 'method', 'url',
                        'code']
        mobile_count = (data.groupby(['gender', 'mobile']).size()).reset_index(name='count')
        mobile_count['mobile'] = mobile_count['mobile'].map(DataDIC.mobile_dic)
        mobile_count = mobile_count.sort_values(by='count', ascending=False)
        self.dataUtil.save_data_to_csv(mobile_count, '../data/most_popular_mobile_by_gender.csv')
        # 男（女）使用手机排名的单独前十
        mobile_count_male = mobile_count[mobile_count['gender'] == '男'].head(10)
        mobile_count_female = mobile_count[mobile_count['gender'] == '女'].head(10)
        # 将mobile_male和mobile_female的数据连接起来
        mobile_count_total = pd.concat([mobile_count_male, mobile_count_female], ignore_index=True)
        self.dataUtil.save_data_to_db(mobile_count_total, 'most_popular_mobile_by_gender')

    # 从csv文件中读取数据，并统计出不同状态码出现次数
    def query_count_network_status(self):
        data = self.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv')
        data.columns = ['ip', 'province', 'city', 'access', 'mobile', 'browser', 'gender', 'age', 'method', 'url',
                        'code']
        network_status_count = data.groupby('code').size().reset_index(name='count')
        print(network_status_count)
        network_status_count['code'] = network_status_count['code']
        self.dataUtil.save_data_to_csv(network_status_count, '../data/network_status_count.csv')
        self.dataUtil.save_data_to_db(network_status_count, 'network_status_count')

    # 按时间段分析访问情况
    def query_access_by_time_period(self):
        data = self.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv')
        data.columns = ['ip', 'province', 'city', 'access', 'mobile', 'browser', 'gender', 'age', 'method', 'url',
                        'code']

        # 将访问时间转换为datetime类型
        data['access_time'] = pd.to_datetime(data['access'])

        # 提取小时信息
        data['hour'] = data['access_time'].dt.hour

        # 定义时间段
        def get_time_period(hour):
            if 5 <= hour < 8:
                return '凌晨时段(5:00-7:59)'
            elif 8 <= hour < 12:
                return '上午时段(8:00-11:59)'
            elif 12 <= hour < 14:
                return '中午时段(12:00-13:59)'
            elif 14 <= hour < 18:
                return '下午时段(14:00-17:59)'
            elif 18 <= hour < 22:
                return '晚上时段(18:00-21:59)'
            else:
                return '深夜时段(22:00-4:59)'

        # 为每个访问记录分配时间段
        data['time_period'] = data['hour'].apply(get_time_period)

        # 统计各时间段访问量
        time_period_count = data.groupby('time_period').size().reset_index(name='count')

        # 计算各时间段访问占比
        time_period_count['ratio'] = time_period_count['count'] / time_period_count['count'].sum()

        # 按访问量排序
        time_period_count = time_period_count.sort_values(by='count', ascending=False)

        # 保存结果
        self.dataUtil.save_data_to_csv(time_period_count, '../data/access_by_time_period.csv')
        self.dataUtil.save_data_to_db(time_period_count, 'access_by_time_period')

        # 按时间段和性别分析访问情况
        time_gender_count = data.groupby(['time_period', 'gender']).size().reset_index(name='count')
        time_gender_count = time_gender_count.sort_values(by=['time_period', 'count'], ascending=[True, False])
        self.dataUtil.save_data_to_csv(time_gender_count, '../data/time_period_by_gender.csv')
        self.dataUtil.save_data_to_db(time_gender_count, 'time_period_by_gender')

        # 按时间段和省份分析访问情况
        time_province_count = data.groupby(['time_period', 'province']).size().reset_index(name='count')
        time_province_count['province'] = time_province_count['province'].map(DataDIC.province_dic)
        time_province_count = time_province_count.sort_values(by=['time_period', 'count'], ascending=[True, False])
        self.dataUtil.save_data_to_csv(time_province_count, '../data/time_period_by_province.csv')
        self.dataUtil.save_data_to_db(time_province_count, 'time_period_by_province')

        return time_period_count

    # 按小时分析访问情况
    def query_access_by_hour(self):
        data = self.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv')
        data.columns = ['ip', 'province', 'city', 'access', 'mobile', 'browser', 'gender', 'age', 'method', 'url',
                        'code']

        # 将访问时间转换为datetime类型
        data['access_time'] = pd.to_datetime(data['access'])

        # 提取小时信息
        data['hour'] = data['access_time'].dt.hour

        # 统计每小时访问量
        hour_count = data.groupby('hour').size().reset_index(name='count')

        # 计算每小时访问占比
        hour_count['ratio'] = hour_count['count'] / hour_count['count'].sum()

        # 保存结果
        self.dataUtil.save_data_to_csv(hour_count, '../data/access_by_hour.csv')
        self.dataUtil.save_data_to_db(hour_count, 'access_by_hour')

        return hour_count


if __name__ == '__main__':
    dataAnalysis = DataAnalysis()
    # dataAnalysis.dataUtil.clean_data(dataAnalysis.dataUtil.read_data_from_csv('../data/nginx_2024053100.csv'))
    # dataAnalysis.query_province_user_count()
    # dataAnalysis.query_province_user_count_by_gender()
    # dataAnalysis.query_gender_ratio()
    # dataAnalysis.query_most_popular_mobile()
    # dataAnalysis.query_most_popular_browser()
    # dataAnalysis.query_most_popular_mobile_by_gender()
    # dataAnalysis.query_count_network_status()
    dataAnalysis.query_access_by_time_period()
    dataAnalysis.query_access_by_hour()